import openpyxl
import sqlite3
import json
import os
import pathlib
from tkinter import messagebox
from pathlib import Path
from datetime import datetime

#创建一个新的工作簿
workBook = openpyxl.Workbook()
#获取活动的工作表（第一个工作表）
sheet = workBook.active

#保存excel文件
def saveFile(date):
    try :
        dateTime = datetime.now()
        dateTime = dateTime.strftime('%H-%M-%S')
        #获取当前工作目录的路径
        # strm = pathlib.Path(__file__).parent.resolve()
        # # strm += "/导出数据"
        # date_path = str(strm)
        # date_path = "./导出数据"
        #判断文件夹是否存在
        if not os.path.exists(f"./导出数据") :
            os.makedirs(f"./导出数据")

        workBook.save(f"./导出数据/%s %s的打靶数据.xlsx"%(date,dateTime,))
        #文件存在，弹出提示框
        if os.path.exists(f"./导出数据/%s %s的打靶数据.xlsx"%(date,dateTime,)) :
            messagebox.showinfo("提示","导出成功！")
        else :
            messagebox.showinfo("提示","导出失败！")
    except Exception as e:
        print(e)


#将查询到的信息写入excel表中
def start(arg,bahao,fenzu,time):
    msgSize = len(arg) 
    #查询一个人的数据
    if bahao!=0 and fenzu!=0:
        #环数列表
        cn_list = list()
        for data in arg:
            cn_list.append(data[1])
        
        sheet.cell(2,1,bahao)
        sheet.cell(2,2,fenzu)
        
        if len(arg) >0:
            # name = arg[0]['user_name']
            sheet.cell(2,3,'root')

        #在工作表中写入数据
        i,sum = 0,0
        for cn in cn_list:
            sum+=cn
            sheet.cell(2,i+4,cn)
            i+=1
        sheet.cell(2,34,sum)
    #查询指定靶号的各个分组数据
    elif bahao!=0 and fenzu ==0 :
        group_list = list()
        # name_list = list()
        #得到名字和分组的信息
        for data in arg:
            group_list.append(data[2])
            # name_list.append(data['user_name'])
        #分组相同，是同一个靶
        index_i = 0
        index_j = index_i+1
        while index_i < len(group_list):
            while index_j < len(group_list):
                if group_list[index_j]==group_list[index_i]:
                    group_list.pop(index_j)
                    index_j-=1
                index_j+=1
            index_i+=1

        # index_i0 = 0
        # index_j0 = index_i0+1
        # while index_i0 < len(name_list):
        #     while index_j0 < len(name_list):
        #         if name_list[index_j0]==name_list[index_i0]:
        #             name_list.pop(index_j0)
        #             index_j0-=1
        #         index_j0+=1
        #     index_i0+=1

        #在工作表中写入数据
        j = 0
        while j < len(group_list):
            cn_group_list = list()
            for datas in arg:
                if group_list[j] == datas[2]:
                    cn_group_list.append(datas[1])
            sheet.cell(j+2,1,bahao)
            sheet.cell(j+2,2,group_list[j])

            # if len(name_list)>j:
            #     sheet.cell(j+2,3,name_list[j])
            # else:
            sheet.cell(j+2,3,'root')
            col,sum1 = 0
            for cn in cn_group_list:
                sum1+=cn
                sheet.cell(j+2,col+4,cn)
                col+=1
            sheet.cell(j+2,34,sum1)

            j+=1
    #查询指定分组的所有靶号数
    elif fenzu!=0 and bahao==0:
        addr_list = list()
        name_bahao_list = list()
        #得到名字和分组的信息
        for data1 in arg:
            addr_list.append(data1[0])
            # name_bahao_list.append(data1['user_name'])
        
        index_i_bahao = 0
        index_j_bahao = index_i_bahao+1
        while index_i_bahao < len(addr_list):
            while index_j_bahao < len(addr_list):
                if addr_list[index_j_bahao]==addr_list[index_i_bahao]:
                    addr_list.pop(index_j_bahao)
                    index_j_bahao-=1
                index_j_bahao+=1
            index_i_bahao+=1

        # index_i0_baha0 = 0
        # index_j0_bahao = index_i0_baha0+1
        # while index_i0_baha0 < len(name_bahao_list):
        #     while index_j0_bahao < len(name_bahao_list):
        #         if name_bahao_list[index_j0_bahao]==name_bahao_list[index_i0_baha0]:
        #             name_bahao_list.pop(index_j0_bahao)
        #             index_j0_bahao-=1
        #         index_j0_bahao+=1
        #     index_i0_baha0+=1
        
        j0 = 0
        while j0 < len(addr_list):
            cn_bahao_list = list()
            for data2 in arg:
                if group_list[j0] == data2[2]:
                    cn_bahao_list.append(data2[1])
            sheet.cell(j0+2,1,addr_list[j0])
            sheet.cell(j0+2,2,fenzu)

            # if len(name_bahao_list)>j0:
            #     sheet.cell(j0+2,3,name_bahao_list[j0])
            # else:
            sheet.cell(j0+2,3,'root')
            cols,sum2 = 0,0
            for cn1 in cn_group_list:
                sum2+=cn1
                sheet.cell(j0+2,cols+4,cn1)
                cols+=1
            sheet.cell(j0+2,34,sum2)

            j0+=1
    #保存工作簿到文件
    saveFile(time)

#数据库操作
def handleSqlite(groupnumber,addr,date):
    try :
        targetBulletInfo = {}
        # current_path_db = pathlib.Path(__file__).parent.resolve()
        #连接数据库
        conn = sqlite3.connect(f'./autoTarget.db')
    
        cur = conn.cursor()
        #sqlite查询语句
        # sql_query_text = ''

        if groupnumber == 0 :
            # cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE addr = ? and create_at = ?",addr,date)
            cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (addr = ? and create_at = ?);".format(addr,date))
            # sql_query_text = "SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (addr = '{0}' and create_at = '{2}'".format(addr,date)
        elif addr == 0 :
            cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (group_number = '{0}' and create_at = '{1}');".format(groupnumber,date))
            # cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE group_number = ? and create_at = ?",groupnumber,date)
            # sql_query_text = "SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (group_number = '{0}' and create_at = '{1}'".format(groupnumber,date)
        else :
            cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (addr = '{0}' and group_number = '{1}'  and create_at = '{2}');".format(addr,groupnumber,date))
            # cur.execute("SELECT addr,cylinder_number,group_number FROM target_info_table WHERE addr = ? and group_number = ? and create_at = ?",addr,groupnumber,date)
            # sql_query_text = "SELECT addr,cylinder_number,group_number FROM target_info_table WHERE (addr = '{0}' and group_number = '{1}'  and create_at = '{2}'".format(addr,groupnumber,date)
             
        targetBulletInfo = cur.fetchall()

    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()
        start(targetBulletInfo,addr,groupnumber,date)

#读取json文件,获取查询信息查询数据库
def readJson():
    try :
        # current_path = pathlib.Path(__file__).parent.resolve()
        # str_path = str(current_path)
        # str_path += "/assets"
        with open(f"./assets/config.json",'r') as file_config:
            #解析json文件内容
            file_data = json.load(file_config)

            searchData = file_data['searchDate']
            groupNumber = searchData['searchDateGroupNumber']
            addr = searchData['searchDateAddr']
            date = searchData['searchDateDate']
        #操作数据库
        handleSqlite(groupNumber,addr,date)    
    except Exception as e :
        print(e)

def main():
    sheet.cell(1,1,'靶号')
    sheet.cell(1,2,'分组')
    sheet.cell(1,3,'姓名')

    readJson()

main()
